package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.UserLogin;

/**
 * A class used for managing User_Login
 */
public class UserLoginDAO {
    /** a database of this user_login */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param db a database
     */
    public UserLoginDAO(Database db) {
        this.db = db;
    }

    /**
     * finding PName + FName + LName
     * @param pnSiteId site
     * @param pnUserId user_id
     * @return PName + FName + LName
     */
    public String findNameByUserId(int pnSiteId, int pnUserId) {
        String vaSql = "SELECT user_pname, user_fname, user_lname FROM user_login WHERE site_id = ? and user_id = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnUserId);
        if (result != null) {
        	String paAnswer = result.get("USER_PNAME").toString() + " " + result.get("USER_FNAME").toString()
        			+ " " + result.get("USER_LNAME").toString();
            return paAnswer;
        } else {
            return "ไม่มีข้อมูลผู้ใช้ระบบ";
        }
    }
    /**
     * finding all user by siteId
     * @return all user where site_id = :id
     */
    public List<Map<String, Object>>  findAllUserNameBySiteId(int pnSiteId) {
        String vaSql = "SELECT * FROM user_login WHERE site_id = "+pnSiteId+" ";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
        	return result;
        } else {
            return null;
        }
    }
    
    

	/**
	 * for Login
	 * 
	 * @param paUserName
	 * @param paUserPassword
	 * @return Map<String, Object>
	 */
	public Map<String, Object> Login(String paUserName, String paUserPassword) {
		
		String vaSql = "SELECT user_login.site_id , user_login.user_id, user_login.user_name, user_login.user_password, user_login.email_address,"+
							"sm_role.role_id, sm_role.role_name, user_login.user_position ,user_login.user_pname,"+
							"user_login.user_fname , user_login.user_lname , user_login.user_status"+
							" FROM user_login, sm_role"+
							" WHERE sm_role.role_id = (SELECT role_id FROM sm_user_role WHERE user_id = user_login.user_id) "+
							"AND user_login.user_name=? AND user_login.user_password=?";
			Map<String, Object> result = db.querySingle(vaSql, paUserName,paUserPassword);
			if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
		
	}
	/**
	 * finding LoginKey
	 * 
	 * @param pnUserId
	 * @return LoginKey
	 */
	public String findLoginKeyByUserId(int pnUserId) {
		
			String vaSql = "SELECT login_key FROM user_login WHERE user_id=?";
			Map<String, Object> result = db.querySingle(vaSql, pnUserId);
			if (result != null) {
	            return result.get("LOGIN_KEY").toString();
	        } else {
	            return null;
	        }
			
	}
	/**
	 * finding LoginKey
	 * 
	 * @param pnUserId
	 * @return LoginKey
	 */
	public String findPasswordKeyByUserId(int pnUserId) {
		
			String vaSql = "SELECT password_key FROM user_login WHERE user_id=?";
			Map<String, Object> result = db.querySingle(vaSql, pnUserId);
			if (result != null) {
	            return result.get("PASSWORD_KEY").toString();
	        } else {
	            return null;
	        }
		
	}
	/**
	 * for findUser
	 * 
	 * @param pnUserId
	 * @return Map<String, Object>
	 */
	public Map<String, Object> findUserByUserId(int pnUserId) {
	
			String vaSql = "SELECT user_id,user_name,email_address,login_key,password_key FROM user_login WHERE user_id=?";
			Map<String, Object> result = db.querySingle(vaSql, pnUserId);
			if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
			
			
	}
	/**
	 * for findUser
	 * 
	 * @param paUserName
	 * @return Map<String, Object>
	 */
	public Map<String, Object> findUserByUserName(String paUserName) {
	
			String vaSql = "SELECT user_id,user_name,email_address,login_key,password_key FROM user_login WHERE user_name=?";
			Map<String, Object> result = db.querySingle(vaSql, paUserName);
			if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
			
			
	}
	/**
	 * for Update Login Key
	 * 
	 * @param pnUserId
	 */
	public void updateLoginKey(int pnUserId) {
		String vaCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
		String vaLoginKey="";
		for(int i=0;i<10;i++){	
			vaLoginKey += vaCharacters.charAt((int)(Math.random() *62 ));			
		}
		
			String vaSql = "UPDATE user_login SET login_key=? WHERE user_id=?";
			db.update(vaSql,vaLoginKey,pnUserId);
	}
	/**
	 * for Update Password Key
	 * 
	 * @param pnUserId
	 */
	public void updatePasswordKey(int pnUserId) {
		String vaCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
		String vaPasswordKey="";
		for(int i=0;i<10;i++){	
			vaPasswordKey += vaCharacters.charAt((int)(Math.random() *62 ));			
		}
		
			String vaSql = "UPDATE user_login SET password_key=? WHERE user_id=?";
			db.update(vaSql,vaPasswordKey,pnUserId);
			
	}
	/**
	 * for Update Password 
	 * 
	 * @param pnUserId
	 */
	public void updatePassword(int pnUserId,String paPassword) {
	
			String vaSql = "UPDATE user_login SET user_password=? WHERE user_id=?";
			db.update(vaSql,paPassword,pnUserId);
			
	}
	
	// Louis 5-11-2014
	public String findUserPasswordByUserId(int pnUserId) 
	{
		String vaSql = "SELECT USER_PASSWORD FROM user_login WHERE user_id=?";
		Map<String, Object> result = db.querySingle(vaSql, pnUserId);
		if (result != null && result.size() > 0) {
            return result.get("USER_PASSWORD").toString();
        } else {
            return null;
        }
	}
	
	// Louis 5-11-2014
	public boolean updateUserPasswordByUserId(int pnUserId, String paUserPassword) 
	{
		String vaSql = "update user_login set USER_PASSWORD = ? where user_id = ? ";
		
		try
		{
			db.update(vaSql, paUserPassword, pnUserId);
			return true;
		}
		catch(Exception ex)
		{
			return false;
		}
	}
	
		// เพิ่มข้อมูล UserLogin 
		public boolean insertUserLogin(UserLogin poNewUserLogin) {
	        String vaSql = "INSERT INTO user_login ( "
	      		  + "site_id, user_id, user_pname, user_fname, user_lname, birthday, home_number, address, city_code, district_code, province_code, "
	      		  + "post_code, telephone, email_address, start_working, end_working, user_position, head_position, user_status, "
	      		  + "user_name, user_password, password_key, login_key, insert_user_id, insert_date, insert_time, update_user_id) "
	      		  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	        boolean result = false;
	        try {
	        	db.update(vaSql,
	        			poNewUserLogin.getVnSiteId(),
	        			poNewUserLogin.getVaUserId(),
	        			poNewUserLogin.getVaUserPName(),
	        			poNewUserLogin.getVaUserFName(),
	        			poNewUserLogin.getVaUserLName(),
	        			poNewUserLogin.getVaBirthDay(),
	        			poNewUserLogin.getVaHomeNumber(),
	        			poNewUserLogin.getVaAddress(),
	        			poNewUserLogin.getVaCityCode(),
	        			poNewUserLogin.getVaDistrictCode(),
	        			poNewUserLogin.getVaProvinceCode(),
	        			poNewUserLogin.getVaPostCode(),
	        			poNewUserLogin.getVaTelephone(),
	        			poNewUserLogin.getVaEmailAddress(),
	        			poNewUserLogin.getVaStartWorking(),
	        			poNewUserLogin.getVaEndWorking(),
	        			poNewUserLogin.getVaUserPosition(),
	        			poNewUserLogin.getVaHeadPosition(),
	        			poNewUserLogin.getVaUserStatus(),
	        			poNewUserLogin.getVaUsername(),
	        			poNewUserLogin.getVaUserPassword(),
	        			poNewUserLogin.getVaPasswordKey(),
	        			poNewUserLogin.getVaLoginKey(),
	        			poNewUserLogin.getVnInsertUserId(),
	        			poNewUserLogin.getVaInsertDate(),
	        			poNewUserLogin.getVaInsertTime(),
	        			poNewUserLogin.getVnUpdateUserId()
	        			);
	        	result = true;
	        } catch (Exception e) {
	        	result = false;
	        }
	        return result;
		}
	
		public int getCountAllUserLoginByUserId(String paUserId, String paUserFName) {
			int i = 0;
	   		String vaSql = "SELECT COUNT(*)"
	   					+ " FROM user_login";
	   					if (!paUserId.equals("") || !paUserFName.equals("")) {
	   						vaSql += " WHERE";
	   					}
	   					if (!paUserId.equals("")) {
	   						if (i != 0) {
	   							vaSql += " AND";
	   						}
	   						vaSql += " user_id = '" + paUserId + "'";
	   						i++;
	   					}
	   					if (!paUserFName.equals("")) {
	   						if (i != 0) {
	   							vaSql += " AND";
	   						}
	   						vaSql += " (LOWER(user_fname) LIKE LOWER('%" + paUserFName + "%') "
	   							+ " OR LOWER(user_lname) LIKE LOWER('%" + paUserFName + "%'))";
	   						i++;
	   					}
	   					Map<String, Object> result = db.querySingle(vaSql);
	   			        if (result != null) {
	   			        	return Integer.parseInt(result.get("COUNT(*)").toString());
	   			        } else {
	   			            return 0;
	   			        }
	   	}
		
		public List<Map<String, Object>> findAllUserLoginByUserId(String paUserId, String paUserFName, String paSortColumn) {
			int i = 0;
	   		String vaSql = "SELECT user_id, user_pname || ' ' || user_fname || ' ' || user_lname AS user_fullname, user_position, telephone,"
	   					+ " CASE user_status WHEN '0' THEN 'InActive' ELSE 'Active' END AS status"
	   					+ " FROM user_login ";
			   			if (!paUserId.equals("") || !paUserFName.equals("")) {
							vaSql += " WHERE";
						}
						if (!paUserId.equals("")) {
							if (i != 0) {
								vaSql += " AND";
							}
							vaSql += " user_id = '" + paUserId + "'";
							i++;
						}
						if (!paUserFName.equals("")) {
							if (i != 0) {
								vaSql += " AND";
							}
							vaSql += " (LOWER(user_fname) LIKE LOWER('%" + paUserFName + "%')"
		   						+ " OR LOWER(user_lname) LIKE LOWER('%" + paUserFName + "%'))";
							i++;
						}
	   					vaSql	+= " ORDER BY " + paSortColumn;
				List<Map<String, Object>> result = db.queryList(vaSql);
				return result;
	   	}
		
		public List<Map<String, Object>> findUserLoginByUserId(String userId, String userFName, String paSortColumn, int pnStart, int pnEnd) {
			int i = 0;
	   		String vaSql = "SELECT user_id, user_pname || ' ' || user_fname || ' ' || user_lname AS user_fullname, user_position, telephone,"
	   					+ " CASE user_status WHEN '0' THEN 'InActive' ELSE 'Active' END AS status"
	   					+ " FROM user_login ";
			   			if (!userId.equals("") || !userFName.equals("") || pnStart != -1) {
							vaSql += " WHERE";
						}
						if (!userId.equals("")) {
							if (i != 0) {
								vaSql += " AND";
							}
							vaSql += " user_id = '" + userId + "'";
							i++;
						}
						if (!userFName.equals("")) {
							if (i != 0) {
								vaSql += " AND";
							}
							vaSql += " (LOWER(user_fname) LIKE LOWER('%" + userFName + "%')"
		   						+ " OR LOWER(user_lname) LIKE LOWER('%" + userFName + "%'))";
							i++;
						} 
						if (i != 0) {
							vaSql += " AND ROWNUM <= " + pnEnd + " AND ROWNUM > " + pnStart + "";
						} else {
							vaSql += " ROWNUM <= " + pnEnd + " AND ROWNUM > " + pnStart + "";
						}
	   					vaSql	+= " ORDER BY " + paSortColumn;
				List<Map<String, Object>> result = db.queryList(vaSql);
				return result;
	   	}
		
		public boolean deleteUser(String userId) {
	        String vaSql = "UPDATE user_login SET user_status = '0' WHERE user_id = ?";
	        try {
	            db.update(vaSql, userId);
	            return true;
	        } catch (Exception e) {
	            return false;
	        }
	    }
		
		public Map<String, Object> getUserLoginAllForEdit(String pnUserId) {
			
			String vaSql = "SELECT * FROM user_login WHERE user_id = ?";
			Map<String, Object> result = db.querySingle(vaSql, pnUserId);
			if (result != null) {
	            return result;
	        } else {
	            return null;
	        }
		
		}
		
		// แก้ไขข้อมูล UserLogin 
		public boolean updateUserLogin(UserLogin poNewUserLogin) {
	        String vaSql = "UPDATE user_login SET "
	      		  + "site_id = ?, user_pname = ?, user_fname = ?, user_lname = ?, birthday = ?, home_number = ?, address = ?, city_code = ?, "
	      		  + "district_code = ?, province_code = ?, post_code = ?, telephone = ?, email_address = ?, start_working = ?, "
	      		  + "end_working = ?, user_position = ?, head_position = ?, user_status = ?, "
	      		  + "user_name = ?, update_user_id = ?, update_date = ?, update_time = ? "
	      		  + "WHERE user_id = ?";
	        boolean result = false;
	        try {
	        	db.update(vaSql,
	        			poNewUserLogin.getVnSiteId(),
	        			poNewUserLogin.getVaUserPName(),
	        			poNewUserLogin.getVaUserFName(),
	        			poNewUserLogin.getVaUserLName(),
	        			poNewUserLogin.getVaBirthDay(),
	        			poNewUserLogin.getVaHomeNumber(),
	        			poNewUserLogin.getVaAddress(),
	        			poNewUserLogin.getVaCityCode(),
	        			poNewUserLogin.getVaDistrictCode(),
	        			poNewUserLogin.getVaProvinceCode(),
	        			poNewUserLogin.getVaPostCode(),
	        			poNewUserLogin.getVaTelephone(),
	        			poNewUserLogin.getVaEmailAddress(),
	        			poNewUserLogin.getVaStartWorking(),
	        			poNewUserLogin.getVaEndWorking(),
	        			poNewUserLogin.getVaUserPosition(),
	        			poNewUserLogin.getVaHeadPosition(),
	        			poNewUserLogin.getVaUserStatus(),
	        			poNewUserLogin.getVaUsername(),
	        			poNewUserLogin.getVnUpdateUserId(),
	        			poNewUserLogin.getVaUpdateDate(),
	        			poNewUserLogin.getVaUpdateTime(),
	        			poNewUserLogin.getVaUserId()
	        			);
	        	result = true;
	        } catch (Exception e) {
	        	result = false;
	        }
	        return result;
		}
		
		public boolean updateUserLogin(String poLoginDate , String poLoginTime , int poUserId) {
	        String vaSql = "UPDATE user_login SET "
	      		  + "login_date = ?, login_time = ? "
	      		  + "WHERE user_id = ?";
	        boolean result = false;
	        try {
	        	db.update(vaSql,poLoginDate,poLoginTime,poUserId);
	        	result = true;
	        } catch (Exception e) {
	        	result = false;
	        }
	        return result;
		}
		
		public boolean findAuthority(int poUserID , String poMenuURL,String poMenuID,String poSrvlName) {
			
			String t = "";
			String[] chkMainpage = poMenuURL.split("/CS");
			String mainPageName = "";
			if (chkMainpage.length > 1) {
				mainPageName = chkMainpage[0]
						+ "/CS"
						+ (Integer.parseInt(chkMainpage[1].replace(".jsp", "")) - 1)
						+ ".jsp";
			}

			Map<String, Object> result = null;
			try{
				
				if(poMenuID.equals("") || poMenuID.equals(null)){

					t = "select count(*) AS AMOUNT_MENU "+
								"from USER_LOGIN ul join SM_USER_ROLE ur on ul.user_id = ur.user_id "+
													"join SITES s on ul.site_id = s.site_id "+
													"join SM_ROLE r on ur.role_id = r.role_id and s.site_id = r.site_id "+
													"join SM_PACKAGE pk on s.package_id = pk.package_id "+
													"join SM_MAPPING m on pk.package_id = m.package_id and r.role_id = m.role_id "+
													"join SM_MENU mn on m.menu_id = mn.menu_id "+
			                  	"where ul.user_id = ? and (mn.MENU_URL = ? OR mn.MENU_URL = ?)";
					
					result = db.querySingle(t,poUserID,poMenuURL,poSrvlName);	
				}else{
					t = "select count(*) AS AMOUNT_MENU "+
							"from USER_LOGIN ul join SM_USER_ROLE ur on ul.user_id = ur.user_id "+
												"join SITES s on ul.site_id = s.site_id "+
												"join SM_ROLE r on ur.role_id = r.role_id and s.site_id = r.site_id "+
												"join SM_PACKAGE pk on s.package_id = pk.package_id "+
												"join SM_MAPPING m on pk.package_id = m.package_id and r.role_id = m.role_id "+
												"join SM_MENU mn on m.menu_id = mn.menu_id "+
		                  	"where ul.user_id = ? and mn.MENU_ID = ?";
				
					result = db.querySingle(t,poUserID,poMenuID);
				}
								
			}catch(Exception e){				
				e.printStackTrace();
			}
		
			if (result == null) {
				t = "select count(*) AS AMOUNT_MENU "+
						"from USER_LOGIN ul join SM_USER_ROLE ur on ul.user_id = ur.user_id "+
											"join SITES s on ul.site_id = s.site_id "+
											"join SM_ROLE r on ur.role_id = r.role_id and s.site_id = r.site_id "+
											"join SM_PACKAGE pk on s.package_id = pk.package_id "+
											"join SM_MAPPING m on pk.package_id = m.package_id and r.role_id = m.role_id "+
											"join SM_MENU mn on m.menu_id = mn.menu_id "+
	                  	"where ul.user_id = ? and (mn.MENU_URL = ? OR mn.MENU_URL = ?)";
			
			result = db.querySingle(t,poUserID,mainPageName,poSrvlName);
				
				if(result.get("AMOUNT_MENU").toString().equals("0")){
					return false;
				}else{
					return true;
				}
			}else{
				return true;
			}
			
			
		}
}
